package com.sds.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sds.util.Util_DB;
import com.sds.vo.ScheduleVO;

public class ScheduleDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	public void insertSchedule(ScheduleVO schedule) {

		try {
			conn = Util_DB.connection();
			if (conn == null) {
				System.out
						.println("Connection Error - [ Location : Schedule ]");
			} else {
				pstmt = conn
						.prepareStatement("insert into t_schedule values(?, ?, ?, ?, ?)");

				pstmt.setInt(1, schedule.getSch_code());
				pstmt.setString(2, schedule.getSch_line_id());
				pstmt.setString(3, schedule.getSch_bus_id());
				pstmt.setDate(4, schedule.getSch_d_time());
				pstmt.setDate(5, schedule.getSch_a_time());
				
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}

	public ScheduleVO selectSchedule(int sch_code) {

		ScheduleVO schedule = null;
		try {
			conn = Util_DB.connection();
			if (conn == null) {
				System.out
						.println("Connection Error - [ Location : Schedule ]");
			} else {
				pstmt = conn
						.prepareStatement("select * from t_schedule where sch_code = ?");
				// pstmt =
				// conn.prepareStatement("select * from t_schedule where sch_code = 22");

				System.out.println("sch_code:" + sch_code);
				pstmt.setInt(1, sch_code);
				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {

					schedule = new ScheduleVO(rs.getInt(1), rs.getString(2),
							rs.getString(3), rs.getDate(4), rs.getDate(5));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(rs, pstmt, conn);
		}
		return schedule;
	}

	public ArrayList<ScheduleVO> selectScheduleList() {
		ArrayList<ScheduleVO> scheduleList = new ArrayList<ScheduleVO>();

		try {
			conn = Util_DB.connection();
			if (conn == null) {
				System.out
						.println("Connection Error - [ Location : Schedule ]");
			} else {
				pstmt = conn
						.prepareStatement("select * from t_schedule order by sch_code");

				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {
					scheduleList.add(new ScheduleVO(rs.getInt(1), rs
							.getString(2), rs.getString(3), rs.getDate(4), rs
							.getDate(5)));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(rs, pstmt, conn);
		}
		return scheduleList;
	}

	public void updateMember(ScheduleVO schedule) {
		try {
			conn = Util_DB.connection();
			if (conn == null) {
				System.out
						.println("Connection Error - [ Location : Schedule ]");
			} else {
				pstmt = conn
						.prepareStatement("update t_schedule set sch_line_id = ?, sch_bus_id = ?, sch_d_time = ?, sch_a_time where sch_code = ?");
				pstmt.setString(1, schedule.getSch_line_id());
				pstmt.setString(2, schedule.getSch_bus_id());
				pstmt.setDate(3, schedule.getSch_d_time());
				pstmt.setDate(4, schedule.getSch_a_time());
				pstmt.setInt(5, schedule.getSch_code());
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}

	public void deleteSchedule(String sch_code) {
		try {
			conn = Util_DB.connection();
			if (conn == null) {
				System.out
						.println("Connection Error - [ Location : Schedule ]");
			} else {
				pstmt = conn
						.prepareStatement("delete t_schedule where sch_code = ?");
				pstmt.setString(1, sch_code);
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}
}
